import datetime
import os
import sys
sys.path.append(os.getcwd())
from src.utils.config import logger
from src.utils.db_processor import presto_processor,mysql_price_model
from src.utils.util import get_today, check_date_str, format_date_string
from src.utils.model_utils import get_laptop_discont_mapping

SETTLE_DATA = """
select
  dp.product_brand_id as product_brand_id,
  dp.product_brand_name as product_brand_name,
  5 as category_id,
  current_date-1 as settle_document_create_date,
  count(1) as product_amount_num,
  sum(res.saleprice) as settle_amount_num_sum,
  -1 as platform_reference_price_sum,
  count(1) as cnt,
  round(1+sum(price_diff_spu_wt)/greatest(1,sum(saleprice_spu_wt)) + sum(amt*lost_ration_turnover)/greatest(1,sum(amt)),5) as percent,
  round(1+sum(price_diff_spu_wt)/greatest(1,sum(saleprice_spu_wt)) + sum(amt*lost_ration_turnover)/greatest(1,sum(amt)),5) as discont_rate,
  case when round(1+sum(price_diff_spu_wt)/greatest(1,sum(saleprice_spu_wt)) + sum(amt*lost_ration_turnover)/greatest(1,sum(amt)),5)>1.03 then 1.03
       when round(1+sum(price_diff_spu_wt)/greatest(1,sum(saleprice_spu_wt)) + sum(amt*lost_ration_turnover)/greatest(1,sum(amt)),5)<0.97 then 0.97
       else round(1+sum(price_diff_spu_wt)/greatest(1,sum(saleprice_spu_wt)) + sum(amt*lost_ration_turnover)/greatest(1,sum(amt)),5) end final_discont_rate
 from
	c2b.doba_quote_price_index_to_lastday_result_verion2 res
    inner join dim.dim_product dp on res.product_id=dp.product_id and dp.product_category_id=5
where
	res.product_category_parent_name in ('笔记本')
	and res.adjust_hit_tag = '0命中'
group by 1,2

"""

def load_settle_data(model_date=None):
    """
    读取结算数据
    :param model_date: 建模日期，如果为None，默认为当天
    :return:
    """
    if model_date is None:
        model_date = get_today()
    else:
        model_date = check_date_str(model_date)
    # start_date = model_date - datetime.timedelta(days=MODEL_DAYS)
    # d_start = datetime.datetime.strptime('2020-02-10', '%Y-%m-%d')
    # if(start_date<d_start):
    #     diff=MODEL_DAYS+21
    #     start_date = model_date - datetime.timedelta(days=diff)
    logger.info('loading settle data from {}'.format(model_date))
    start_date = format_date_string(model_date)
    sql = SETTLE_DATA
    load_data= presto_processor.load_sql(sql, parse_dates=['settle_document_create_date'])
    load_data['settle_document_create_date']=load_data['settle_document_create_date'].apply(lambda x:x.strftime('%Y-%m-%d'))
    return load_data




def save_complement_data(load_data,model_date=None):
        """
        保存补全数据
        :return:
        """
        if load_data is not None:
            logger.info('saving model_c_laptop_discont data...')
            # template_level_mapping = self.__load_template_level_mapping()
            # template_level_mapping = template_level_mapping.rename(columns={'level_id': 'product_level_id'})
            # insert_data = pd.merge(self.predict_data, template_level_mapping,
            #                        on=['product_level_template_id', 'product_level_id'])
            # dt = datetime.datetime.strptime(time.strftime('%Y-%m-%d'), '%Y-%m-%d') + datetime.timedelta(days=1)
            if model_date is None:
                model_date = get_today()
            else:
                model_date = check_date_str(model_date)
            start_date = format_date_string(model_date)
            delete_sql = '''
                       delete from algorithm_model_c_laptop_discont where settle_document_create_date=DATE_FORMAT(DATE_SUB(date('{}') ,INTERVAL 1 DAY),'%Y-%m-%d');
                       '''
            #删除已经存在的当天数据
            dlsql = delete_sql.format(start_date)
            mysql_price_model.execute_sql(dlsql)
            #插入当天的数据
            insert_sql = """
            INSERT INTO algorithm_model_c_laptop_discont( settle_document_create_date,
            product_amount_num, category_id, settle_amount_num_sum, platform_reference_price_sum,
            product_brand_id, product_brand_name, cnt, percent, discont_rate,
            final_discont_rate)
            VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            mysql_price_model.execute_insert_sql(insert_sql,
                                                 load_data[
                                                     ['settle_document_create_date',
            'product_amount_num', 'category_id', 'settle_amount_num_sum', 'platform_reference_price_sum',
            'product_brand_id', 'product_brand_name', 'cnt', 'percent', 'discont_rate',
            'final_discont_rate']
                                                 ].to_records(index=False).tolist())
            logger.info('saving load_data data to mysql done size={}'.format(load_data.shape[0]))

def main():
    brand_discont_map=get_laptop_discont_mapping()
    current_hour = datetime.datetime.now().hour
    load_data=load_settle_data()
    save_complement_data(load_data)
    logger.info('saving discont  load_data done')

if __name__ == '__main__':
    main()